<?php
/**
 * Created by PhpStorm.
 * User: zhangrxiang
 * Date: 2017/6/30
 * Time: 9:52
 * Project name: PHPTEST
 * File name: ZingSQLite.php
 * @copyright Copyright (c) 2016 ZRX Software LLC
 * @license https://github.com/zhangrxiang
 * @since 1.0.0
 */

$SQLite3 = new SQLite3( "test.db", SQLITE3_OPEN_READWRITE | SQLITE3_OPEN_CREATE );
if ( ! $SQLite3 ) {
	echo $SQLite3->lastErrorMsg();
	exit();
}
$exist = $SQLite3->querySingle( "SELECT COUNT(*) FROM sqlite_master where type='table' and name='stu'" );
if ( ! $exist ) {
	$result = $SQLite3->exec( "CREATE TABLE stu([id] INTEGER PRIMARY KEY AUTOINCREMENT ,[name] VARCHAR NOT NULL ,[age] INT);" );
	if ( $result ) {
		echo "create success" . PHP_EOL;
	}
}

$sql = "SELECT ID FROM stu ORDER BY ROWID DESC LIMIT 1;";
$id  = $SQLite3->querySingle( $sql );
if ( $id < 100 ) {
	for ( $i = $id; $i < 100; $i ++ ) {
		$str = md5( $i );
		$SQLite3->exec( "INSERT INTO stu (name, age) VALUES ('" . $str . "'," . $i . ");" );
	}
}

$result = $SQLite3->query( "select * from stu" );
while ( $row = $result->fetchArray() ) {
	echo "id :" . $row['id'] . nl2br(PHP_EOL);
	echo "name : " . $row['name'] . nl2br(PHP_EOL);
	echo "age : " . $row['age'] . nl2br(PHP_EOL);
}

$delete = $SQLite3->exec("delete from stu WHERE id > 30");
echo "delete :".$delete.nl2br(PHP_EOL);
